﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace MoneyClass
{
    public class User
    {
        /// <summary>
        /// 获取所有用户列表
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable getAll()
        {
            string sql = "select [guid],[userid],[pass],[username],[enable],iif([Enable]=true,'启用','禁用') as status,iif([Sex]=true,'男','女') as xb,[comment] from [QT_User] order by [userid] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取允许登陆的用户列表
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable getUsing()
        {
            string sql = "select [guid],[userid],[pass],[username],[comment] from [QT_User] where [enable]=true order by [userid] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <returns>DataTable</returns>
        public static DataTable get(string guid)
        {
            string sql = "select [guid],[userid],[pass],[UserName],[comment] from [QT_User] where [guid]='" + guid + "'";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 检查用户是否管理员
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <returns>bool</returns>
        public static bool IsAdmin(string guid)
        {
            string sql = "select [isadmin] from [QT_User] where [guid]='" + guid + "'";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            if (dt.Rows.Count > 0 && dt.Rows[0][0].ToString().ToLower() == "true") return true;
            else return false;
        }

        /// <summary>
        /// 用户登陆
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <param name="userid">用户名</param>
        /// <param name="password">加密后的密码</param>
        /// <returns>int：0为用户不存在，1为登陆成功，-1为用户名错误，-2为密码错误，99为用户信息系统异常</returns>
        public static int Login(string guid,string userid,string password)
        {
            string sql = "select [userid],[pass] from [QT_User] where [guid]='" + guid + "'";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            if (dt.Rows.Count == 0) return 0;
            if (dt.Rows.Count > 1) return 99;
            if (dt.Rows[0][0].ToString().ToLower() != userid.ToLower()) return -1;
            if (dt.Rows[0][1].ToString().ToLower() != password.ToLower()) return -2;
            return 1;
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <param name="pass">密码</param>
        /// <param name="username">姓名</param>
        /// <param name="enable">是否启用</param>
        /// <param name="sex">性别：男为true，女为false</param>
        /// <param name="comment">备注</param>
        /// <returns>int</returns>
        public static int Add(string userid, string pass, string username,string comment, bool enable, bool sex)
        {
            if (Exists(null, userid)) return -2;

            string sql = "insert into [QT_User]([guid],[userid],[pass],[username],[comment],[enable],[sex]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + userid + "','" + pass + "','" + username + "','" + comment + "'," + (enable ? "true" : "false") + "," + (sex ? "true" : "false") + ")";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <param name="userid">用户ID</param>
        /// <param name="pass">密码</param>
        /// <param name="username">姓名</param>
        /// <param name="enable">是否启用</param>
        /// <param name="sex">性别：男为true，女为false</param>
        /// <param name="isadmin">是否管理员</param>
        /// <param name="comment">备注</param>
        /// <returns>int</returns>
        public static int Update(string guid, string userid, string pass, string username, string comment, bool enable, bool sex)
        {
            if (Exists(guid, userid)) return -2;

            string sql = "update [QT_User] set [userid]='" + userid + "'" + (string.IsNullOrEmpty(pass) ? "" : (",[pass]='" + pass + "'")) + ",[username]='" + username + "',[enable]=" + (enable ? "true" : "false") + ",[sex]=" + (sex ? "true" : "false") + ",[comment]='" + comment + "' where [guid]='" + guid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <param name="pass">修改后的密码</param>
        /// <param name="Opass">修改前的密码</param>
        /// <returns>int</returns>
        public static int Update(string guid, string pass,string Opass)
        {
            string sql = "update [QT_User] set [pass]='" + pass + "' where [guid]='" + guid + "' and [pass]='" + Opass + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="guids">一组用户ID</param>
        /// <returns>int</returns>
        public static int Delete(List<string> guids)
        {
            string sql = "delete from [QT_User] where [guid] in('"+string.Join("','",guids.ToArray())+"')";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 检查用户名
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <param name="userid">用户ID</param>
        /// <returns>bool</returns>
        public static bool Exists(string guid,string userid)
        {
            string sql = "select [guid] from [QT_User] where [userid]='" + userid + "'" + (string.IsNullOrEmpty(guid) ? "" : " and [guid]<>'" + guid + "'");
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            else return false;
        }
    }
}
